<?php
// +----------------------------------------------------------------------
// | ThinkPHP [ WE CAN DO IT JUST THINK ]
// +----------------------------------------------------------------------
// | Copyright (c) 2006~2017 http://thinkphp.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: 李邦琦 <1536549251@qq.com>
// +----------------------------------------------------------------------

namespace app\crm\model;
use think\Db;
use think\Model;
use think\Cache;
use think\Loader;

class Logisticsreport extends Model
{
    /**
     * 物流报表数据
     * @return array    物流报表数据
     */
    public function logisticsReportData ()
    {
        $page = $_REQUEST["page"];
        $limit = $_REQUEST["limit"];
        $createtime_start = $_REQUEST['createtime_start'];
        $createtime_end = $_REQUEST['createtime_end'];
        $dept_id = $_REQUEST['dept_id'];
        $create_user = $_REQUEST['create_user'];
        $username = $_SESSION["user_info"]["username"];
        $d_id = $_SESSION['user_info']['d_id'];
        // $arrDep = $this->getDepTreeData();
        $arrDep = Loader::model('Reportmanager')->getDepTreeData();
        // $deptst = $this->getMeAndSubDeptID($arrDep,$dept_id);  //取上级部门
        $deptst = Loader::model('Reportmanager')->getMeAndSubDeptID($arrDep,$dept_id);  //取上级部门
        $deptSet = rtrim($deptst,",");
        
        $deptst2 = Loader::model('Reportmanager')->getMeAndSubDeptID($arrDep,$d_id);;  //取上级部门
        $deptSet2 = rtrim($deptst2,",");
        
        if(!$dept_id){
            $deptId = $deptSet2;
        }else{
            $deptId = $deptSet;
        }
        $where = "1 ";
        $where .= empty($createtime_start) ? "" : " AND createtime >= '$createtime_start'";
        $where .= empty($createtime_end) ? "" : " AND createtime <= '$createtime_end'";
        $where .= empty($create_user) ? "" : " AND createname = '$create_user'";
        if($username != "admin" || $dept_id){
            $where .= " AND dept_id in ($deptId)";
        }
        // print_r($where);exit;


        $fields = "createname,dept_id,
            sum(case when shopping_name='1' AND (shipping_status = '1' OR shipping_status = '2' OR (shipping_status='0' and order_status='4')) then 1 else 0 end) as shunfeng_send_num,
            sum(case when shopping_name='3' AND (shipping_status = '1' OR shipping_status = '2' OR (shipping_status='0' and order_status='4')) then 1 else 0 end) as ems_send_num,
            sum(case when shopping_name='5' AND (shipping_status = '1' OR shipping_status = '2' OR (shipping_status='0' and order_status='4')) then 1 else 0 end) as shentong_send_num,
            sum(case when shopping_name='1' AND shipping_status = '2' AND logistics_state = '3' then 1 else 0 end) as shunfeng_sign_for_num,
            sum(case when shopping_name='3' AND shipping_status = '2' AND logistics_state = '3' then 1 else 0 end) as ems_sign_for_num,
            sum(case when shopping_name='5' AND shipping_status = '2' AND logistics_state = '3' then 1 else 0 end) as shentong_sign_for_num,
            sum(case when shopping_name='1' AND shipping_status = '2' AND logistics_state = '3' then cope_money else 0 end) as shunfeng_sign_for_qmoney,
            sum(case when shopping_name='3' AND shipping_status = '2' AND logistics_state = '3' then cope_money else 0 end) as ems_sign_for_qmoney,
            sum(case when shopping_name='5' AND shipping_status = '2' AND logistics_state = '3' then cope_money else 0 end) as shentong_sign_for_qmoney,
            sum(case when shopping_name='1' AND (shipping_status = '1' OR shipping_status = '2' OR (shipping_status='0' and order_status='4')) then cope_money else 0 end) as shunfeng_send_omoney,
            sum(case when shopping_name='3' AND (shipping_status = '1' OR shipping_status = '2' OR (shipping_status='0' and order_status='4')) then cope_money else 0 end) as ems_send_omoney,
            sum(case when shopping_name='5' AND (shipping_status = '1' OR shipping_status = '2' OR (shipping_status='0' and order_status='4')) then cope_money else 0 end) as shentong_send_omoney ,
            
            sum(case when shipping_status in('1','2','4') or (shipping_status=0 and order_status=4) then 1 else 0 end) as total_send_num,
            sum(case when shipping_status = '1' OR shipping_status = '2' OR (shipping_status='0' and order_status='4') then cope_money else 0 end) as total_send_money,
            sum(case when logistics_state = '3' then cope_money else 0 end) as total_sign_for_money,
            sum(case when logistics_state = '3' then 1 else 0 end) as total_sign_for_num,


            sum(case when logistics_state = '0' then 1 else 0 end) as logistics_state0,
            sum(case when logistics_state = '1' then 1 else 0 end) as logistics_state1,
            sum(case when logistics_state = '2' then 1 else 0 end) as logistics_state2,
            sum(case when logistics_state = '3' then 1 else 0 end) as logistics_state3,
            sum(case when logistics_state = '4' then 1 else 0 end) as logistics_state4,
            sum(case when logistics_state = '5' then 1 else 0 end) as logistics_state5,
            sum(case when logistics_state = '6' then 1 else 0 end) as logistics_state6,
            sum(case when logistics_state = '7' then 1 else 0 end) as logistics_state7
        ";

        // print_r($fields);exit;

        $arrCount = Db::name('order_info')->Distinct(true)->field("createname")->where($where)->select();
        $count = count($arrCount);
        if ($_REQUEST['search_type'] == 'excel') {
            $sql = "SELECT ".$fields." FROM `xy_order_info` WHERE ".$where."  GROUP BY `createname` ORDER BY createname asc";
        } else {
            $sql = "SELECT ".$fields." FROM `xy_order_info` WHERE ".$where."  GROUP BY `createname` ORDER BY createname asc LIMIT ".($page-1)*$limit.",".$limit;
        }
        //$dataList = Db::name('order_info')->fetchSql(true)->field($fields)->where($where)->limit(($page-1)*$limit,$limit)->order("createname asc")->group("createname")->select();
        // echo $sql; exit;
        $dataList = Db::query($sql);
        $department = Cache::get('department');
        $users = Cache::get('users');
        
        foreach($dataList as $key=>&$val){
            $val["dept_name"] = $department[$val["dept_id"]]["department_name"];
            $val["cn_name"] = $users[$val["createname"]]["cn_name"];

            $val["total_sign_for_rate2"] = ($val["total_sign_for_money"]/$val["total_send_money"])*100;
            $val["total_sign_for_rate"] = round($val["total_sign_for_rate2"],2)."%";;  //总签收率

            $val["shunfeng_sign_for_rate2"] = ($val["shunfeng_sign_for_money"]/$val["shunfeng_send_money"])*100;
            $val["shunfeng_sign_for_rate"] = round($val["shunfeng_sign_for_rate2"],2)."%";;  //顺丰签收率

            $val["ems_sign_for_rate2"] = ($val["ems_sign_for_money"]/$val["ems_send_money"])*100;
            $val["ems_sign_for_rate"] = round($val["ems_sign_for_rate2"],2)."%";;  //ems签收率

            $val["shentong_sign_for_rate2"] = ($val["shentong_sign_for_money"]/$val["shentong_send_money"])*100;
            $val["shentong_sign_for_rate"] = round($val["shentong_sign_for_rate2"],2)."%";;  //申通签收率
        }
        // 如果是导出
        if ($_REQUEST['search_type'] == 'excel') {
            $fields = array(
                'cn_name' => '姓名',
                'dept_name' => '部门',
                'logistics_state0' => '在途',
                'logistics_state1' => '揽件',
                'logistics_state2' => '疑难',
                'logistics_state3' => '签收',
                'logistics_state4' => '退签',
                'logistics_state5' => '派件',
                'logistics_state6' => '退回',
                'logistics_state7' => '转投',

            );
            foreach ($dataList as &$val) {
                unset($val['createname']);
                unset($val['dept_id']);
            }
            Loader::model('Customer')->exportExcel($dataList, $fields, '物流报表');
            exit;
        }
        $rowsList = count($dataList) ? $dataList : false;
        $arrCate["code"] = 0;
        $arrCate["msg"] = '';
        $arrCate["count"] = $count;
        $arrCate["data"] = $rowsList;
        return $arrCate;

    }




}